Interview Questions

1. The size of data page in sql server is

Options:

a. 8kb

b. 6kb

c. 7kb

d. 10kb

Reveal Answer

2. How many pages does sql server database have per megabyte?

Options:

a. 180 pages

b. 248 pages

c. 140 pages

d. 128 pages

Reveal Answer

3. Which statements are true regarding data pages

Options:

a. Each page begins with a 196-byte header that is used to store system information about the page.

b. page header has information about the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

c. Every time data changes in the table, all the indexes need to be updated

d. Use of indexes fastens Data modification operations, such as insert, update, delete.

Reveal Answer

4. What does 'B-tree structure' mean?

Options:

a. Basic tree structure

b. Balance tree structure

c. Business tree structure

d. Binary tree structure

Reveal Answer

5. In which of these structures are the data pages put in the leaf node?

Options:

a. Heap structures

b. Non cluster index

c. Cluster index

d. None

Reveal Answer

6. If there is only one non cluster index and no cluster index then the index will refer to

Options:

a. Heap

b. Non cluster index

c. Clustered index

d. None

Reveal Answer

7. In heap the data pages are linked to each other and the rows are inserted in a particular order based on a key.

Options:

a. TRUE

b. FALSE

Reveal Answer

8. In cluster index the data pages are in the

Options:

a. Root node

b. Intermediate nodes

c. Leaf nodes

d. Heap

Reveal Answer

9. Cluster index can be on only one column. It is not allowed on composite columns.

Options:

a. TRUE

b. FALSE

Reveal Answer

10. By default Cluster index is formed on column where _________________ constraint is given.

Options:

a. Unique

b. Check

c. Primary Key

d. Foreign key

Reveal Answer

11. How many non cluster indexes can be created in SQL server ?

Options:

a. 290

b. 555

c. 750

d. 999

Reveal Answer

12. Which statement is true regarding Indexes

Options:

a. More than one cluster indexes can be created

b. When indexes are created the b-tree structure for each index occupies disk space

c. Columns other than the non cluster key can also be included in the leaf node of the non cluster index

d. Creating many indexes fastens the DML actions

Reveal Answer

13. createtablesample ( idintprimarykey, namevarchar(20)unique, salarymoney ) createnonclusteredindexindx_sampleonsample(salary) How many index are created on table 'sample' ?

Options:

a. 1

b. 2

c. 3

d. 4

Reveal Answer

14. createtablesample2 ( idintprimarykey, namevarchar(20), salarymoney ) createuniqueindexunq_indx_sample2onsample2(name) insertintosample2values(1,'jyothi',10000),(2,'poorinima',60000) How many records are inserted into the table 'sample2'

Options:

a. 2 records

b. 1 records

c. No records are inserted

Reveal Answer

15. Which of these are the valid types of indexes

Options:

a. Clustered

b. Unique

c. Spatial

d. Filtered

e. All the above

Reveal Answer

16. How many non cluster indexes can be created in a single table?

Options:

a. 249

b. 250

c. 320

d. 300

Reveal Answer

17. Which statements are true regarding Heap

Options:

a. Heap in an unordered table

b. The rows are inserted in either ascending or descending order

c. Heap is made entirely of data pages .

d. B-tree is created for heap

Reveal Answer

18. The fill-factor option is provided for fine-tuning index data storage and performance.

Options:

a. TRUE

b. FALSE

Reveal Answer

19. When an index is created or rebuilt, the filling factor value determines

Options:

a. The percentage of space on each leaf level data page to be reserved as free space for future growth

b. The percentage of space on each leaf level data page to be filled with data

c. The fill factor is not related to indexes or data pages so it does not effect the occupancy or free space of the data pages

d. None of the above

Reveal Answer

20. If the fill-factor value is 80 mean

Options:

a. 20 percent of each leaf -level page will be left empty

b. 80 percent of each leaf -level page will be left empty

c. 20 percent of each leaf -level page will be occupied with data

d. 80 percent of each leaf -level page will be occupied with data

Reveal Answer

Interview Questions and Answer

1. The size of data page in sql server is

2. How many pages does sql server database have per megabyte?

3. Which statements are true regarding data pages

4. What does 'B-tree structure' mean?

5. In which of these structures are the data pages put in the leaf node?

6. If there is only one non cluster index and no cluster index then the index will refer to

7. In heap the data pages are linked to each other and the rows are inserted in a particular order based on a key.

8. In cluster index the data pages are in the

9. Cluster index can be on only one column. It is not allowed on composite columns.

10. By default Cluster index is formed on column where _________________ constraint is given.

11. How many non cluster indexes can be created in SQL server ?

12. Which statement is true regarding Indexes

13. createtablesample ( idintprimarykey, namevarchar(20)unique, salarymoney ) createnonclusteredindexindx_sampleonsample(salary) How many index are created on table 'sample' ?

14. createtablesample2 ( idintprimarykey, namevarchar(20), salarymoney ) createuniqueindexunq_indx_sample2onsample2(name) insertintosample2values(1,'jyothi',10000),(2,'poorinima',60000) How many records are inserted into the table 'sample2'

15. Which of these are the valid types of indexes

16. How many non cluster indexes can be created in a single table?

17. Which statements are true regarding Heap

18. The fill-factor option is provided for fine-tuning index data storage and performance.

19. When an index is created or rebuilt, the filling factor value determines

20. If the fill-factor value is 80 mean

Packages

Trending Certification Courses:

Microsoft Mock Exams:

Other links

About

Resources